/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jsysacc.asistencia.model.dao.component.maestros;

import com.cerd.util.BaseSP01.JUtilBaseSP01;
import com.jsysacc.asistencia.model.beans.maestros.Horario;
import com.jsysacc.asistencia.model.dao.design.maestros.IHorarioDAO;
import com.jsysacc.asistencia.model.dao.ds.Cnx;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import org.joda.time.DateTime;

/**
 *
 * @author Carlos
 */
public class HorarioDAO extends JUtilBaseSP01 implements IHorarioDAO {

    private Connection cnx = null;
    private PreparedStatement pst = null;
    private Statement stm = null;
    private ResultSet rs = null;
    private CallableStatement cs = null;

    @Override
    public boolean insert(Horario entity) throws SQLException {
        String sql = " { ? = CALL " + Cnx.getSchema() + ".sp_t_horarios_insert(  ?, ? , ? ,?, ?, "
                + " ?, ? , ? ,?, ?, ?, ? , ? ,?, ?, ?, ?, ?, ?) } ";
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            cs = cnx.prepareCall(sql);
            int pos=1;
            cs.registerOutParameter(pos++, Types.BOOLEAN);
            cs.setString(pos++, entity.getCod_hor());
            cs.setString(pos++, entity.getDescrip());
            cs.setString(pos++, entity.getHor_cor_lv());
            cs.setString(pos++, entity.getTol_man_lv());
            cs.setString(pos++, entity.getHor_ini_man_lv());
            cs.setString(pos++, entity.getHor_fin_man_lv());
            cs.setString(pos++, entity.getTol_tar_lv());
            cs.setString(pos++, entity.getHor_ini_tar_lv());
            cs.setString(pos++, entity.getHor_fin_tar_lv());
            cs.setString(pos++, entity.getHor_cor_sab());
            cs.setString(pos++, entity.getTol_man_sab());
            cs.setString(pos++, entity.getHor_ini_man_sab());
            cs.setString(pos++, entity.getHor_fin_man_sab());
            cs.setString(pos++, entity.getTol_tar_sab());
            cs.setString(pos++, entity.getHor_ini_tar_sab());
            cs.setString(pos++, entity.getHor_fin_tar_sab());
            cs.setString(pos++, entity.getEstado());
            cs.setString(pos++, entity.getUsu_reg());
            cs.setTimestamp(pos++, toTimestampSQL(DateTime.now()));
            cs.execute();
            if (!cs.getBoolean(1)) {
                throw new SQLException("ERROR AL INSERTAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (cs != null) {
                    cs.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public boolean update(Horario entity) throws SQLException {
        String sql = " { ? = CALL " + Cnx.getSchema() + ".sp_t_horarios_update( ? , ? ,?, ?, "
                + " ?, ? , ? ,?, ?, ?, ? , ? ,?, ?, ?, ?, ? ) } ";
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            cs = cnx.prepareCall(sql);
            int pos=1;
            cs.registerOutParameter(pos++, Types.BOOLEAN);
            cs.setString(pos++, entity.getCod_hor());
            cs.setString(pos++, entity.getDescrip());
            cs.setString(pos++, entity.getHor_cor_lv());
            cs.setString(pos++, entity.getTol_man_lv());
            cs.setString(pos++, entity.getHor_ini_man_lv());
            cs.setString(pos++, entity.getHor_fin_man_lv());
            cs.setString(pos++, entity.getTol_tar_lv());
            cs.setString(pos++, entity.getHor_ini_tar_lv());
            cs.setString(pos++, entity.getHor_fin_tar_lv());
            cs.setString(pos++, entity.getHor_cor_sab());
            cs.setString(pos++, entity.getTol_man_sab());
            cs.setString(pos++, entity.getHor_ini_man_sab());
            cs.setString(pos++, entity.getHor_fin_man_sab());
            cs.setString(pos++, entity.getTol_tar_sab());
            cs.setString(pos++, entity.getHor_ini_tar_sab());
            cs.setString(pos++, entity.getHor_fin_tar_sab());
            cs.setString(pos++, entity.getEstado());
            cs.execute();
            if (!cs.getBoolean(1)) {
                throw new SQLException("ERROR AL MODIFICAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (cs != null) {
                    cs.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public boolean delete(String pk) throws SQLException {
        String sql = " { ? = CALL " + Cnx.getSchema() + ".sp_t_horarios_delete( ? ) } ";
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            cs = cnx.prepareCall(sql);
            cs.registerOutParameter(1, Types.BOOLEAN);
            cs.setString(2, pk);
            cs.execute();
            if (!cs.getBoolean(1)) {
                throw new SQLException("ERROR AL ELIMINAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (cs != null) {
                    cs.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public ArrayList<Horario> getAll() throws SQLException {
        ArrayList<Horario> lista = new ArrayList<>();
        Horario oHorario = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_horarios_get_all() ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                oHorario = new Horario();
                mapearBeanForRS(rs, oHorario);
                lista.add(oHorario);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public ArrayList<Horario> getByCriterio(String criterio, String valor) throws SQLException {
        ArrayList<Horario> lista = new ArrayList<>();
        Horario oHorario = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_horarios_get_by_criterio(?, ?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, criterio);
            pst.setString(2, valor);
            rs = pst.executeQuery();
            while (rs.next()) {
                oHorario = new Horario();
                mapearBeanForRS(rs, oHorario);
                lista.add(oHorario);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public Horario getByPk(String pk) throws SQLException {
        Horario oHorario = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_horarios_get_by_pk(?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, pk);
            rs = pst.executeQuery();
            while (rs.next()) {
                oHorario = new Horario();
                mapearBeanForRS(rs, oHorario);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return oHorario;
    }

}
